#!/usr/bin/python2.4
#
# Copyright 2011 Google Inc. All Rights Reserved.

from BeautifulSoup import BeautifulSoup as BS
from BeautifulSoup import Tag

from controllers import geocode, ftclient
import datetime
import logging
import urllib
import re

from google.appengine.ext import db
from google.appengine.ext import webapp
from google.appengine.ext.webapp.util import run_wsgi_app

import csv
TABLE = "396048" #"394015"
COLUMNS = ['geolocation', 'name', 'time', 'description', 'icon']

class FusionTableUser(db.Model):
  user = db.StringProperty(required=True)
  passwd = db.StringProperty(required=True)

# The very, very, VERY!!, loose formatting is
#
# <h3>region</h3>
# <strong>street</strong> - description
# ...

# strong sections which we are blacklisting because they aren't relevant
blacklist = [
  'WARNINGS: Roads not closed, but drivers should approach with caution',
  'Melbourne to Adelaide - Western Highway now open'
]

# some strong sections have both the road and description within the strong tag so split on ' - ' however whitelist these to exclude
split_whitelist = [
  'Ballarat - Burrumbeet Road',
  'Horsham-Drung Road (Williams Road) - C'
]

# Format the street which in theory is the content between <strong> tags
def extract_street(str):
  if str is None:
    return None

  str = fix(str)
  if len(str) == 0 or str in blacklist:
    return None

  # bugfix
  if str.endswith('- C'):
    str = str[0:len(str) - 3].strip()

  # hack - some strong content encloses a tailing hyphen
  if str.endswith('-'):
    str = str[0:len(str) - 1].strip()

  # another hack - there is this massive long "advisery" in the middle so lets just ignore anything over 75 chars
  if len(str) > 50:
    return None

  # another... some of the road closures include the description as part of the string information (which is why we return a tuple)
  if str.find(' - ') != -1 and not (str in split_whitelist):
    return str.split(' - ')

  return (str, None)

# Format the description which is in theory the text following the strong block
def extract_desc(str):
  if str is None:
    return None

  str = fix(str)
  
  # Strip off a leading hypen
  if str.startswith('-'):
    str = str[1:].strip()

  # bug fix for "Horsham-Drung Road (Williams Road) - C"
  if str.startswith('losed at'):
    str = 'C' + str

  # readability fix
  if str.startswith('at ') or str.startswith('under ') or str.startswith('between ') or str.startswith('from '):
    str = 'Closed ' + str

  # visual fix
  return str

def fix(str):
  # random-ness pruning
  # remove \' to avoid FT insert query problems
  return str.replace('<br />','').replace('&nbsp;', ' ').replace('<span style="color: #000000;">', '').replace('</span>', '').replace("'",'').replace('&amp;','&').strip()

def cap(str):
  if str is None or len(str) == 0:
    return str

  result = str[0].upper()
  if len(str) > 1:
    result = result + str[1:]

  return result

def make_closure(road, desc):
  return {
    'geolocation': road + ', Victoria, Australia',
    'name': road,
    'time': 'Deprecated',
    'description': cap(str(desc)),
    'icon': 'Deprecated'
  }
  #print '"' + road + ', Victoria, Australia","' + road + '","' + str(now) + '","' + str(desc) + '"'

def loadRoads():
  bs = BS(urllib.urlopen("http://mobiletraffic.vicroads.vic.gov.au/floodalerts/").read())
  #bs = BS(urllib.urlopen("vicinput.html").read())

  data = []

  closures = bs.fetch('strong')
  for closure in closures:

    # another hack
    if closure.renderContents().startswith('General Message') or (closure.parent is not None and closure.parent.name == 'em'):
      continue

    # hack because in some parts multiple road closure entries are within the single <strong> tag
    if closure.string is None:
      for x in closure.renderContents().split('<br />'):
        result = extract_street(x)
        if result is not None:
          data.append(make_closure(result[0], None))
      continue

    result = extract_street(closure.string)
    if result is not None:
      (road, desc) = result

      # "standard" case
      if desc is None:

        # some hacks for roads without descriptions
        if road.endswith('Axedale'):
          desc = None
        else:
          desc = extract_desc(closure.findNextSibling(text = True))

      # more hacks for region outside of strong
      if desc is not None and desc.startswith(','):
        road = road + desc
        desc = None

      data.append(make_closure(road, desc))

  updateTable(TABLE, COLUMNS, data)


def updateTable(table, columns, entries):
  
  password = FusionTableUser.all().filter("user = ", "vicroadclosures@gmail.com").get().passwd
  #password = open("password-vic.txt").read().strip()
  
  authtoken = ftclient.GoogleClientLogin('vicroadclosures@gmail.com',password)

  ft = ftclient.FTClient(authtoken)

  query = "select rowid, %s from %s" % (', '.join(columns), table)
  print query
  out = ft.runGetQuery(query)

  existing = {}

  for line in csv.reader(out.split('\n')):
    if not line: continue
    exist = [x.decode('utf-8') for x in line[1:]]

    if line[0] == 'rowid': continue
    existing[line[0]] = exist
  new = []
  for line in entries:
    e = []
    for c in columns: e.append(line[c])
    new.append(e)

  to_remove = []
  to_add = []
  to_keep = []
  print "existing: %s new: %s"%(len(existing), len(new))

  for rowid, entry in existing.items():
    if entry in new:
      # keep
      to_keep.append(entry)
    else:
      to_remove.append(rowid)

  for entry in new:
    if entry in existing.values():
      # keep
      pass
    else:
      to_add.append(entry)

  print "remove: %s add: %s keep: %s" %(len(to_remove), len(to_add),
                                        len(to_keep))

  for rowid in to_remove:
    query = "delete from %s where rowid = '%s'"%(table, rowid)
    print query
    ft.runGetQuery(query)

  column_s = ', '.join(columns)
  for data in to_add:
    values = [x.replace("'", "\\'") for x in data]
    values = ', '.join("'%s'"%x for x in values)

    query = "insert into %s (%s) VALUES (%s)"%(table, column_s, values)
    print query
    ft.runGetQuery(query)

  # Once off to add a new icon.
  #for data in to_keep:
  #  query = "update %s SET icon = 'placemark_circle_highlight' WHERE rowid =
  #  '%s'" % (table, data[1])
  #  print query 
  #  ft.runGetQuery(query)
  
# TODO(arb):
# Hook up handler
# Hook up a cache thingy that rewrites the icon (ask bst)

class UpdatePage(webapp.RequestHandler):
  def get(self):
    loadRoads()
    self.response.headers['Content-Type'] = 'text/plain'
    self.response.out.write('OK')

application = webapp.WSGIApplication([('/vicroads/update__', UpdatePage),
                                      ], debug=True)

def main():
  run_wsgi_app(application)

if __name__ == "__main__":
  loadRoads()
